IF EXISTS (SELECT *
           FROM INFORMATION_SCHEMA.ROUTINES
           WHERE ROUTINE_NAME='pInProdDetail_Add'
           AND ROUTINE_SCHEMA='dbo'
           AND ROUTINE_TYPE='PROCEDURE'
          )
          DROP PROCEDURE dbo.pInProdDetail_Add
GO

CREATE PROC dbo.pInProdDetail_Add
	@productId INT,
	@amount INT,
	@inputPrice INT,
	@inPaperId INT,
	@createdBy INT = NULL,
	@date DATETIME = NULL
AS
/*@@*_____________________________________________________________________

  

_____________________________________________________________________*@@*/

/*______________________ CONSTANT DECLARATIONS _________________________*/


/*______________________ VARIABLE DECLARATIONS _________________________*/

  DECLARE
    @intErrNo       INT,
    @strErrMsg      NVARCHAR(400),
    @intReturn      INT,
	@id				INT

/*_______________________________ INIT _________________________________*/

  -- Setting NOCOUNT ON prevents additional recordsets with the
  -- "number of records affected" from being returned, which is not
  -- handled the same by all providers, such as for OLEDB vs. ODBC.
  SET NOCOUNT ON

/*________________________ VALIDATE PARAMETERS _________________________*/


/*_______________________________ MAIN _________________________________*/
	BEGIN TRANSACTION 
	BEGIN TRY		
		-- Create InProdPaper if it not exists
		IF NOT EXISTS (SELECT '' FROM InProdPaper WHERE Id = @inPaperId)
		BEGIN
			SET @date = ISNULL(@date, GETDATE())
			
			INSERT INTO InProdPaper (Date, CreatedBy)
			VALUES (@date, @createdBy)
			SET	@inPaperId = SCOPE_IDENTITY()
		END
		
		-- Insert new in prod detail
		INSERT INTO InProdDetail (ProductId, Amount, InputPrice, InProdPaperId)
		VALUES (@productId, @amount, @inputPrice, @inPaperId)
		SET @id = SCOPE_IDENTITY()
	
		-- Update amount of product
		UPDATE Product
		SET Amount = (Amount + @amount)
		WHERE Id = @productId
		
		-- Update latest input price of product
		EXEC pUpdateLatestInputPriceOfProduct
			@vintProductId = @productId		
		
		-- Update input price of bill detail
		UPDATE BillDetail
		SET InputPrice = @inputPrice
		FROM BillDetail
		INNER JOIN Bill
		ON BillDetail.BillId = Bill.Id
		WHERE Bill.IsPrinted = 1 
			AND BillDetail.ProductId = @productId 
			AND (BillDetail.InputPrice IS NULL OR BillDetail.InputPrice = 0)
		
		SET @intReturn = 0
		COMMIT TRANSACTION
			
	END TRY
	BEGIN CATCH
		SET @intReturn = -1
		ROLLBACK TRANSACTION
	END CATCH
		
	SELECT Id = @id,
		InProdPaperId = @inPaperId,
		ReturnValue = @intReturn

lblExit:
	RETURN @id 
/*___________________________ ERROR HANDLER ____________________________*/
lblError:
  -- An error has occurred.  On entering this section @intErrNo should
  -- already hold the error number

  -- By default, if there are any open transactions then roll them back
  IF (@@TRANCOUNT > 0) BEGIN
    ROLLBACK TRAN

  END

  -- If return value has not been explicitly set, then use the error
  -- number as return value to signify failure
  IF ((@intReturn IS NULL) OR (@intReturn = 0)) BEGIN
    SET @intReturn = @intErrNo

  END

 GOTO lblExit


